package com.pitaya.winwine.repository;

import java.util.List;

import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;

import com.pitaya.winwine.domain.Tag;

public interface TagRepository extends CrudRepository<Tag, Integer> {
	
	public List<Tag> findByOrderByTypeAscIdAsc();
	
	@Query(nativeQuery = true, value = "SELECT t.id, t.name, null as type, count FROM (SELECT tagId, COUNT(1) AS count FROM r_topic_tag GROUP BY tagId ORDER BY COUNT(1) DESC LIMIT ?) r INNER JOIN tb_tag t ON r.tagId=t.id")
	public List<Tag> findByTopicHotTagByLimit(int limit);
	
	@Query(nativeQuery = true, value = "SELECT t.id, t.type, t.name, IF(r.id IS NULL, 0, 1) AS status FROM tb_tag t LEFT JOIN r_topic_tag r ON t.id=r.tagId AND r.topicId=? ORDER BY t.type, t.id")
	public List<Tag> findByTopicIdByStatus(Integer topicId);
}
